package com.android.panballgame;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {
	// Database Version
	private static final int DATABASE_VERSION = 1;
	// Database Name
	private static final String DATABASE_NAME = "pbgame.db";
	// Contacts Table Columns names
	private static final String KEY_ID = "id";

	public DatabaseHandler(Context context) {

		super(context, DATABASE_NAME, null, DATABASE_VERSION);

	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_TABLE = "CREATE TABLE " + PBEngine.MAIN_TABLE  + "(" + KEY_ID 
				+ " INTEGER PRIMARY KEY , level INTEGER NOT NULL , sound INTEGER, music INTEGER)";
		
		String CREATE_LEVEL1 = "CREATE TABLE " + PBEngine.LEVEL_TABLE +
				" (id_level INTEGER PRIMARY KEY, time REAL, time_level1 REAL, time_level2 REAL, " +
				"time_level3 REAL, ballx REAL, bally REAL, " +
				"coinloc INTEGER, coin1 INTEGER, coin2 INTEGER, coin3 INTEGER, coin4 INTEGER, " +
				"coin5 INTEGER, coin6 INTEGER, coin7 INTEGER, coin8 INTEGER, coin9 INTEGER, " +
				"itemloc INTEGER, item1 INTEGER, item2 INTEGER, remain INTEGER,  FOREIGN KEY(id_level) REFERENCES " 
				+ PBEngine.MAIN_TABLE + "("+KEY_ID+") ON DELETE CASCADE)";

		db.execSQL(CREATE_TABLE);
		db.execSQL(CREATE_LEVEL1);
		db.execSQL("CREATE TRIGGER fk_level_main_i BEFORE INSERT ON " + PBEngine.LEVEL_TABLE + 
				 " FOR EACH ROW BEGIN "+			 
				     " SELECT CASE WHEN ((SELECT "+KEY_ID+" FROM "+PBEngine.MAIN_TABLE+
				     " WHERE "+KEY_ID+" =new.id_level ) IS NULL)"+			 
				     " THEN RAISE (ABORT,'insert Foreign Key Violation') END;"+			 
				     "  END;");

		db.execSQL("CREATE TRIGGER fk_level_main_u BEFORE UPDATE ON " + PBEngine.LEVEL_TABLE + 
				" FOR EACH ROW BEGIN " +
				 " SELECT CASE WHEN ((SELECT "+KEY_ID+" FROM "+PBEngine.MAIN_TABLE+
			     " WHERE "+KEY_ID+" =new.id_level ) IS NULL)"+			 
			     " THEN RAISE (ABORT,'update Foreign Key Violation') END;"+			 
			     "  END;");
		
		db.execSQL("CREATE TRIGGER fk_level_main_d BEFORE DELETE ON " + PBEngine.MAIN_TABLE +
				" FOR EACH ROW BEGIN " +
				" DELETE FROM "+ PBEngine.LEVEL_TABLE + 
			    " WHERE id_level = OLD."+ KEY_ID + ";  END;");
	}

	// 버전 업그레이드시 처리
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// 기존 테이블 삭제
		db.execSQL("DROP TABLE IF EXISTS " + PBEngine.MAIN_TABLE );
		db.execSQL("DROP TABLE IF EXISTS " + PBEngine.LEVEL_TABLE);
		db.execSQL("DROP TRIGGER IF EXISTS fk_level_main_i");
		db.execSQL("DROP TRIGGER IF EXISTS fk_level_main_d");
		db.execSQL("DROP TRIGGER IF EXISTS fk_level_main_u");
		// Create tables again
		onCreate(db);

	}
}
